﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace Common.Upload
{
    public class ExcelHelper
    {
        public static void ImportExcelToDB(string excelPath, string excelSheet, string conStr, string tableName)
        {
            DataTable table = getTable(excelPath, excelSheet);
            insertBulkTable(conStr, tableName, table);
        }

        private static DataTable getTable(string path,string sheet)
        {
            try
            {
                string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+path+";Extended Properties=Excel 8.0;";
                
                string sql = "select * from ["+sheet+"]";
                OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn));
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception("读取excel失败！\r\n"+e.Message);
            }
        }

        private static void insertBulkTable(string conStr, string tableName, DataTable data)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conStr;

            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = data.Rows.Count;

            conn.Open();

            try
            {
                if (data != null && data.Rows.Count != 0)
                    bulkCopy.WriteToServer(data);
            }
            catch(Exception e)
            {
                throw new Exception("导入到数据库失败！\r\n" + e.Message);
            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }
    }
}
